import pandas as pd
import database

def load_and_classify(file_path):
    try:
        df = pd.read_excel(file_path)
        df = df[['姓名', '类别', '时间']].copy()
        df.columns = ['name', 'category', 'timestamp']
        df['timestamp'] = pd.to_datetime(df['timestamp'], errors='coerce')
        df = df.dropna(subset=['timestamp'])
        df['timestamp'] = df['timestamp'].dt.strftime('%Y-%m-%d %H:%M:%S')

        conn = database.get_connection()
        df.to_sql('records', conn, if_exists='append', index=False)
        conn.close()
        return True, f"导入 {len(df)} 条数据"
    except Exception as e:
        return False, str(e)

def get_stats_by_weekday():
    conn = database.get_connection()
    df = pd.read_sql('SELECT * FROM records', conn)
    conn.close()

    if df.empty: return {}
    df['timestamp'] = pd.to_datetime(df['timestamp'])
    df['weekday'] = df['timestamp'].dt.day_name()
    return df.groupby('weekday').size().to_dict()

def get_stats_by_date():
    conn = database.get_connection()
    df = pd.read_sql('SELECT * FROM records', conn)
    conn.close()

    if df.empty: return {}
    df['timestamp'] = pd.to_datetime(df['timestamp'])
    df['date'] = df['timestamp'].dt.date
    return df.groupby('date').size().to_dict()